package com.guardchina.framework.util.utils;


import com.guardchina.framework.util.exception.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author lilin
 * @create 2019/11/29 15:00
 */
public class ExcelUtil {


    /**
     * @描述：是否是2003的excel，返回true是2003
     * @时间：2014-08-29 下午16:29:11
     * @参数：@param filePath　文件完整路径
     * @参数：@return
     * @返回值：boolean
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    /**
     * @描述：是否是2007的excel，返回true是2007
     * @时间：2014-08-29 下午16:28:20
     * @参数：@param filePath　文件完整路径
     * @参数：@return
     * @返回值：boolean
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }



    public static String checkValue(Cell cell) {
        String value = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        if(cell == null){
            /*cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue("");*/
            return "";
        }
        try {
            switch (cell.getCellType()){
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue().trim();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    //处理数字和公式
                    value = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
                    value = value.replaceAll("0+?$", "");
                    value = value.replaceAll("[.]$", "");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //处理日期格式的Excel数据
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        value = sdf.format(date);
                    } else {
                        //处理科学计数法
                        double temp = cell.getNumericCellValue();
                       // DecimalFormat df = new DecimalFormat("0");
                        value = ExcelUtil.isIntegerForDouble(temp);
                    }
                    break;
                default:
                    return "";
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("格式错误");
        }
        return value;
    }

    public static Object getCellValue(Cell cell) {
        Object value = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                value= dataFormatter.formatCellValue(cell);
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }



    //判断是否是整数，如果是 则取整数  如果不是 原样返回
    public static String isIntegerForDouble(double obj) {
        double eps = 1e-10;  // 精度范围
        boolean f= obj-Math.floor(obj) < eps;
        if (f){
            return String.valueOf((int) obj);
        }else {
            return String.valueOf(obj);
        }
    }
}
